﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using DBUtility;
using IDAL;
using Model;

namespace SQLServerDAL
{
    public class Order : IOrder
    {
        private const int ORDERSTATE_UNDEAL = 0;
        private const int ORDERSTATE_DEALED = 1;
        private const int ORDERSTATE_CANCEL = 2;

        public OrderInfo SelectOrder(int orderId)
        {
            string sqlSelect = "SELECT O.OrderId,O.OrderDate,O.Username,O.ShipName,O.ShipAddress,O.ShipPhone,O.OrderState,L.ItemISBN,L.ItemName,L.UnitPrice,L.Quantity FROM Orders O INNER JOIN LineItems L ON O.OrderId = L.OrderId WHERE O.OrderId = @OrderId";

            SqlParameter parm = new SqlParameter("@OrderId", SqlDbType.Int);
            parm.Value = orderId;

            SqlDataReader dr = SqlHelper.ExecuteReader(SqlHelper.iBookShopConnStr, CommandType.Text, sqlSelect,parm);

            bool isFirst = true;
            OrderInfo order = null;

            IList<LineItemInfo> items = new List<LineItemInfo>();

            while (dr.Read())
            {
                if (isFirst)
                {
                    order = new OrderInfo(dr.GetInt32(0), dr.GetDateTime(1), dr.GetString(2), dr.GetString(3), dr.GetString(4), dr.GetString(5),dr.GetInt32(6));
                }
                LineItemInfo item = new LineItemInfo(dr.GetString(7), dr.GetString(8), dr.GetDouble(9), dr.GetInt32(10));
                items.Add(item);
            }
            order.LineItems = items;
            dr.Close();
            return order;
        }

        public IList<OrderInfo> SelectByUsername(string username)
        {
            string sqlSelect = "SELECT O.OrderId,O.OrderDate,O.Username,O.ShipName,O.ShipAddress,O.ShipPhone,O.OrderState,L.ItemISBN,L.ItemName,L.UnitPrice,L.Quantity FROM Orders O INNER JOIN LineItems L ON O.OrderId = L.OrderId WHERE O.Username = @Username AND O.OrderState <> @OrderState";

            SqlParameter[] parms = {
                new SqlParameter("@Username", SqlDbType.VarChar, 256),
                new SqlParameter("@OrderState",SqlDbType.Int)};
            parms[0].Value = username;
            parms[1].Value = ORDERSTATE_CANCEL;

            Dictionary<int, OrderInfo> dic = new Dictionary<int, OrderInfo>();

            SqlDataReader dr = SqlHelper.ExecuteReader(SqlHelper.iBookShopConnStr, CommandType.Text, sqlSelect, parms);

            while (dr.Read())
            {
                int orderId = dr.GetInt32(0);
                if (!dic.Keys.Contains<int>(orderId))
                {
                    dic[orderId] = new OrderInfo(orderId, dr.GetDateTime(1), dr.GetString(2), dr.GetString(3), dr.GetString(4), dr.GetString(5), dr.GetInt32(6));
                    dic[orderId].LineItems = new List<LineItemInfo>();
                }
                dic[orderId].LineItems.Add(new LineItemInfo(dr.GetString(7), dr.GetString(8), dr.GetDouble(9), dr.GetInt32(10)));
            }
            dr.Close();
            return dic.Values.ToList<OrderInfo>();
        }

        public IList<OrderInfo> SelectByState(int state)
        {
            string sqlSelect = "SELECT O.OrderId,O.OrderDate,O.Username,O.ShipName,O.ShipAddress,O.ShipPhone,O.OrderState,L.ItemISBN,L.ItemName,L.UnitPrice,L.Quantity FROM Orders O INNER JOIN LineItems L ON O.OrderId = L.OrderId WHERE O.OrderState = @OrderState";

            SqlParameter parm = new SqlParameter("@OrderState", SqlDbType.Int);
            parm.Value = state;

            IDictionary<int, OrderInfo> dic = new Dictionary<int, OrderInfo>();

            SqlDataReader dr = SqlHelper.ExecuteReader(SqlHelper.iBookShopConnStr, CommandType.Text, sqlSelect, parm);

            while (dr.Read())
            {
                int orderId = dr.GetInt32(0);
                if (!dic.Keys.Contains<int>(orderId))
                {
                    dic[orderId] = new OrderInfo(orderId, dr.GetDateTime(1), dr.GetString(2), dr.GetString(3), dr.GetString(4), dr.GetString(5), dr.GetInt32(6));
                    dic[orderId].LineItems = new List<LineItemInfo>();
                }
                dic[orderId].LineItems.Add(new LineItemInfo(dr.GetString(7), dr.GetString(8), dr.GetDouble(9), dr.GetInt32(10)));
            }
            dr.Close();
            return dic.Values.ToList<OrderInfo>();
        }

        public void InsertOrder(OrderInfo order)
        {
            string sqlInsertOrder = "INSERT INTO Orders(OrderDate,UserName,ShipName,ShipAddress,ShipPhone,OrderState) VALUES (@OrderDate,@UserName,@ShipName,@ShipAddress,@ShipPhone,@OrderState); SELECT @@IDENTITY;";

            SqlParameter[] parmsOrders = {
                new SqlParameter("@OrderDate",SqlDbType.DateTime),
                new SqlParameter("@UserName",SqlDbType.VarChar,256),
                new SqlParameter("@ShipName",SqlDbType.VarChar,50),
                new SqlParameter("@ShipAddress",SqlDbType.VarChar,100),
                new SqlParameter("@ShipPhone",SqlDbType.VarChar,50),
                new SqlParameter("@OrderState",SqlDbType.Int)};

            parmsOrders[0].Value = order.OrderDate;
            parmsOrders[1].Value = order.UserName;
            parmsOrders[2].Value = order.ShipName;
            parmsOrders[3].Value = order.ShipAddress;
            parmsOrders[4].Value = order.ShipPhone;
            parmsOrders[5].Value = ORDERSTATE_UNDEAL;

            string sqlInsertLineItems = "INSERT INTO LineItems(OrderId,ItemISBN,ItemName,UnitPrice,Quantity) VALUES (@OrderId,@ItemISBN,@ItemName,@UnitPrice,@Quantity)";

            SqlParameter[] parmsLineItems = {
                new SqlParameter("@OrderId",SqlDbType.Int),
                new SqlParameter("@ItemISBN",SqlDbType.NChar,13),
                new SqlParameter("@ItemName",SqlDbType.VarChar,100),
                new SqlParameter("@UnitPrice",SqlDbType.Float),
                new SqlParameter("@Quantity",SqlDbType.Int)};


            string sqlSelectBook = "SELECT BookRemainNum FROM Books WHERE ISBN=@ISBN";

            SqlParameter parmSelectBook = new SqlParameter("@ISBN", SqlDbType.NChar, 13);

            string sqlUpdateBook = "UPDATE Books SET BookRemainNum=BookRemainNum-@Quantity,BookSoldNum=BookSoldNum+@Quantity WHERE ISBN = @ISBN";
            SqlParameter[] parmsUpdateBook = {
                new SqlParameter("@Quantity",SqlDbType.Int),
                new SqlParameter("ISBN",SqlDbType.NChar,13)};

            SqlConnection conn = new SqlConnection(SqlHelper.iBookShopConnStr);
            conn.Open();

            SqlTransaction trans = conn.BeginTransaction(IsolationLevel.ReadCommitted);

            try
            {
                int orderId = Convert.ToInt32(SqlHelper.ExecuteScalar(trans, CommandType.Text, sqlInsertOrder, parmsOrders));
                foreach (LineItemInfo lineItem in order.LineItems)
                {
                    parmsLineItems[0].Value = orderId;
                    parmsLineItems[1].Value = lineItem.ItemISBN;
                    parmsLineItems[2].Value = lineItem.ItemName;
                    parmsLineItems[3].Value = lineItem.UnitPrice;
                    parmsLineItems[4].Value = lineItem.Quantity;
                    SqlHelper.ExecuteNonQuery(trans, CommandType.Text, sqlInsertLineItems, parmsLineItems);

                    parmSelectBook.Value = lineItem.ItemISBN;

                    int theRemainQuantity = 0;

                    object retVal = SqlHelper.ExecuteScalar(trans,CommandType.Text,sqlSelectBook,parmSelectBook);

                    int.TryParse(retVal.ToString(),out theRemainQuantity);

                    if(theRemainQuantity<lineItem.Quantity)
                    {
                        throw new ApplicationException("Not Enough Book");
                    }
                    else
                    {
                        parmsUpdateBook[0].Value = lineItem.Quantity;
                        parmsUpdateBook[1].Value = lineItem.ItemISBN;
                        SqlHelper.ExecuteNonQuery(trans,CommandType.Text,sqlUpdateBook,parmsUpdateBook);
                    }
                }
                trans.Commit();
            }
            catch (Exception e)
            {
                trans.Rollback();
                throw new ApplicationException(e.Message);
            }
            finally
            {
                conn.Close();
            }
        }

        public void DealOrder(int orderId)
        {
            string sqlUpdate = "UPDATE Orders SET OrderState = @OrderState WHERE OrderId = @OrderId";
            SqlParameter[] parms = {
                new SqlParameter("@OrderState", SqlDbType.Int),
                new SqlParameter("@OrderId",SqlDbType.Int)};

            parms[0].Value = ORDERSTATE_DEALED;
            parms[1].Value = orderId;

            SqlHelper.ExecuteNonQuery(SqlHelper.iBookShopConnStr, CommandType.Text, sqlUpdate, parms);
        }

        public void CancelOrder(int orderId)
        {
            SqlConnection conn = new SqlConnection(SqlHelper.iBookShopConnStr);
            conn.Open();

            SqlTransaction trans = conn.BeginTransaction(IsolationLevel.ReadCommitted);

            string sqlUpdate = "UPDATE Orders SET OrderState = @OrderState WHERE OrderId = @OrderId";
            SqlParameter[] parms = {
                new SqlParameter("@OrderState", SqlDbType.Int),
                new SqlParameter("@OrderId",SqlDbType.Int)};

            parms[0].Value = ORDERSTATE_CANCEL;
            parms[1].Value = orderId;

            string sqlLineItems = "SELECT ItemISBN,Quantity FROM LineItems WHERE OrderId = @OrderId";
            SqlParameter parm = new SqlParameter("@OrderId", SqlDbType.Int);
            parm.Value = orderId;

            string sqlUpdateBookNum = "UPDATE Books SET BookSoldNum = BookSoldNum-@Quantity,BookRemainNum=BookRemainNum+@Quantity WHERE ISBN = @BookISBN";
            SqlParameter[] parmsUpdateBook = {
                new SqlParameter("@Quantity",SqlDbType.Int),
                new SqlParameter("@BookISBN",SqlDbType.NChar,13)};

            try
            {
                SqlHelper.ExecuteNonQuery(trans, CommandType.Text, sqlUpdate, parms);
                SqlDataReader dr = SqlHelper.ExecuteReader(trans,CommandType.Text, sqlLineItems, parm);

                Dictionary<string, int> dic = new Dictionary<string, int>();
                while (dr.Read())
                {
                    string bookISBN = dr.GetString(0);
                    int quantity = dr.GetInt32(1);
                    dic[bookISBN] = quantity;
                }
                dr.Close();
                foreach (string isbn in dic.Keys)
                {
                    parmsUpdateBook[0].Value = dic[isbn];
                    parmsUpdateBook[1].Value = isbn;
                    SqlHelper.ExecuteNonQuery(trans, CommandType.Text, sqlUpdateBookNum, parmsUpdateBook);
                }
                trans.Commit();
            }
            catch (Exception e)
            {
                trans.Rollback();
                throw new ApplicationException(e.Message);
            }
            finally
            {
                conn.Close();
            }
        }
    }
}
